package com.hcrazy.manchester.app.jdbc;

import java.util.ArrayList;
import java.util.List;

import com.hcrazy.manchester.app.pojo.LiveAddress;
import com.hcrazy.manchester.app.pojo.PageModel;

public class LiveAddressDAO extends BaseDAO<LiveAddress>{

	public long addLiveAddress(LiveAddress address) throws Exception{
		StringBuilder sb = new StringBuilder();
		sb.append("insert into him_live_address(");
		sb.append("sortId,title,address,mapKey,phone,workTime,pic,weblink,details,praiseBaseCount,praiseCount,commentBaseCount,commentCount,images");
		sb.append(") values (");
		sb.append(":sortId,:title,:address,:mapKey,:phone,:workTime,:pic,:weblink,:details,:praiseBaseCount,:praiseCount,:commentBaseCount,:commentCount,:images");
		sb.append(")");
		
		update(sb.toString(), address);
		
		return getIncreamentId();
	}
	
	public void updateLiveAddress(LiveAddress address) throws Exception{
		StringBuilder sb = new StringBuilder();
		sb.append("update him_live_address set ");
		sb.append(" sortId=:sortId,title=:title,address=:address,mapKey=:mapKey,");
		sb.append(" phone=:phone,workTime=:workTime,pic=:pic,weblink=:weblink,details=:details,images=:images,");
		sb.append(" praiseBaseCount=:praiseBaseCount,praiseCount=:praiseCount,commentBaseCount=:commentBaseCount,commentCount=:commentCount");
		sb.append(" where infoId = :infoId");
		
		update(sb.toString(), address);
	}
	
	public List<LiveAddress> listAllInfos() throws Exception{
		String sql = "select * from him_live_address";
		return getList(sql, LiveAddress.class);
	}
	
	public List<LiveAddress> getAllInfosBySortId(int sortId) throws Exception{
		String sql = "select a.infoId,a.sortId,a.title,a.address,a.mapKey,a.phone,a.workTime,a.pic,a.weblink,b.sortName from him_live_address a,him_live_sort b where a.sortId = b.sortId and a.sortId = ?";
		return getList(sql, LiveAddress.class,sortId);
	}
	
	public List<LiveAddress> getAllInfosHasDetailBySortId(int sortId) throws Exception{
		String sql = "select a.*,b.sortName from him_live_address a,him_live_sort b where a.sortId = b.sortId and a.sortId = ?";
		return getList(sql, LiveAddress.class,sortId);
	}
	
	public PageModel getPagedInfos(PageModel model,String search,int sortId) throws Exception{
		StringBuilder querySQL = new StringBuilder("select * from (select a.*,b.sortName from him_live_address a,him_live_sort b where a.sortId = b.sortId) as c");
		StringBuilder countSQL = new StringBuilder("select count(*) from (select a.*,b.sortName from him_live_address a,him_live_sort b where a.sortId = b.sortId) as c ");
		StringBuilder whereSQL = new StringBuilder(" where (title like ?  or phone like ?) ");
		List<Object> paramList = new ArrayList<Object>();
		paramList.add("%"+search+"%");
		paramList.add("%"+search+"%");
		if(sortId > 0){
			whereSQL.append(" and sortId = ?");
			paramList.add(sortId);
		}
		return this.getPageModel(model, querySQL, countSQL, whereSQL, paramList, LiveAddress.class);
	}
	
	public LiveAddress getById(int infoId) throws Exception{
		String sql = "select * from him_live_address where infoId = ?";
		return this.getJavaBean(sql, LiveAddress.class, infoId);
	}
	
	public LiveAddress getById(int infoId,boolean hasDetail) throws Exception{
		if(hasDetail){
			return getById(infoId);
		}
		
		String sql = "select * from him_live_address_view where infoId = ?";
		return this.getJavaBean(sql, LiveAddress.class, infoId);
	}
	
	public void delLiveAddress(int infoId) throws Exception{
		String sql = "delete from him_live_address where infoId = ?";
		this.commonUpdate(sql, infoId);
	}
	
	public long getPraiseCount(long infoId) throws Exception{
		String sql = "select praiseCount from him_live_address where infoId = ?";
		return this.getJdbcTemplate().queryForLong(sql,infoId);
	}
	
	public long getPraiseBaseCount(long infoId) throws Exception{
		String sql = "select praiseBaseCount from him_live_address where infoId = ?";
		return this.getJdbcTemplate().queryForLong(sql,infoId);
	}
	
	public long getCommentCount(long infoId) throws Exception{
		String sql = "select commentCount from him_live_address where infoId = ?";
		return this.getJdbcTemplate().queryForLong(sql,infoId);
	}
	
	public long getCommentBaseCount(long infoId) throws Exception{
		String sql = "select commentBaseCount from him_live_address where infoId = ?";
		return this.getJdbcTemplate().queryForLong(sql,infoId);
	}
	
	public void addPraiseCount(long infoId) throws Exception {
		String sql = "update him_live_address set praiseCount = praiseCount + 1 where infoId = ?";
		this.commonUpdate(sql, infoId);
	}
	
	public void addCommentCount(long infoId) throws Exception {
		String sql = "update him_live_address set commentCount = commentCount + 1 where infoId = ?";
		this.commonUpdate(sql, infoId);
	}
	
	/**
	 * 获取收藏信息
	 * @param userId
	 * @return
	 * @throws Exception
	 */
	public PageModel getCollegeAddresses(long userId,int moduleType,PageModel model) throws Exception{
		StringBuilder sql = new StringBuilder("select * from (select a.type,b.* from him_user_collection a,him_live_address b where a.infoId = b.infoId) as m");
		StringBuilder sqlCount = new StringBuilder("select count(*) from (select a.type,b.* from him_user_collection a,him_live_address b where a.infoId = b.infoId) as m");
		StringBuilder whereSQL = new StringBuilder(" where userId = ? and type = ?");
		
		List<Object> paramList = new ArrayList<Object>();
		paramList.add(userId);
		paramList.add(moduleType);
		
		return this.getPageModel(model, sql, sqlCount, whereSQL, paramList, LiveAddress.class);
	}
	
	public List<LiveAddress> getLiveAddresses(int parentSortId,int size) throws Exception{
		String sql = " select a.* from him_live_address a ,him_live_sort b where a.sortId = b.sortId and b.sortParent = ? order by infoId desc limit ?";
		
		return getList(sql,LiveAddress.class,parentSortId,size);
	}
}
